﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// SQLHelper 的摘要说明
/// </summary>
public class SQLHelper
{
    /// <summary>
    /// 连接字符串（不用管加密没加密）
    /// </summary>
    public static string conn = ConfigurationManager.AppSettings["conn"].ToString();
    /// <summary>
    /// 取一个数据集        -用于查找获,取数据集
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="parms">sql参数</param>
    /// <returns>返回DataSet</returns>
    public static DataSet GetDataSet(string sql, params SqlParameter[] parms)
    {
        DataSet ds = null;
        SqlConnection sqlcon = new SqlConnection(conn);
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlcon);
            da.SelectCommand.CommandType = CommandType.Text;
            if (parms != null)
            {
                foreach (SqlParameter pa in parms)
                    da.SelectCommand.Parameters.Add(pa);
            }
            ds = new DataSet();
            da.Fill(ds);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            sqlcon.Close();
        }
        return ds;
    }
    /// <summary>
    /// 取一个SqlDataReader -用于读取数据
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="parms">sql参数</param>
    /// <returns>返回SqlDataReader(dr关闭时sqlcon自动关闭,dr需手动关闭)</returns>
    public static SqlDataReader GetDataReader(string sql, params SqlParameter[] parms)
    {
        SqlDataReader dr = null;
        SqlConnection sqlcon = new SqlConnection(conn);
        try
        {
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            if (parms != null)
            {
                foreach (SqlParameter pa in parms)
                    cmd.Parameters.Add(pa);
            }
            sqlcon.Open();
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception e)
        {
            throw e;
        }
        return dr;
    }
    /// <summary>
    /// 执行一个操          -用于查找单个数据
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="parms">sql参数</param>
    /// <returns>返回一个object</returns>
    public static object ExecuteScalar(string sql, params SqlParameter[] parms)
    {
        object obj = null;
        SqlConnection sqlcon = new SqlConnection(conn);
        try
        {
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            if (parms != null)
            {
                foreach (SqlParameter pa in parms)
                    cmd.Parameters.Add(pa);
            }
            sqlcon.Open();
            obj = cmd.ExecuteScalar();
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            sqlcon.Close();
        }
        return obj;
    }
    /// <summary>
    /// 执行操作            -用于执行插入、修改、删除
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="parms">sql参数</param>
    /// <returns>影响的行数（INT）</returns>
    public static int ExecuteNonQuery(string sql, params SqlParameter[] parms)
    {
        int i = 0;
        SqlConnection sqlcon = new SqlConnection(conn);
        try
        {
            SqlCommand cmd = new SqlCommand(sql, sqlcon);
            if (parms != null)
            {
                foreach (SqlParameter pa in parms)
                    cmd.Parameters.Add(pa);
            }
            sqlcon.Open();
            i = cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            sqlcon.Close();
        }
        return i;
    }

    /// <summary>
    /// 存储过程获取当前页数据.(表名可以用联结)
    /// </summary>
    /// 
    /// @tblName            varchar(255),                --   表名
    /// @RetColumns         varchar(1000) = '*',         --   需要返回的列，默认为全部
    /// @Orderfld           varchar(255),                --   排序字段名
    /// @PageSize           int  =  10,                  --   页尺寸
    /// @PageIndex          int  =  1,                   --   页码
    /// @IsCount            bit  =  0,           	   --   返回记录总数,   非   0   值则返回
    /// @OrderType          varchar(50)  =  'asc',       --   设置排序类型,   非   asc   值则降序
    /// @strWhere           varchar(1000)  =  ''         --   查询条件   (注意:   不要加   where)
    /// 
    /// <param name="parms">必须传入(表名tableName,排序字段名Orderfld).
    /// 可以传入的字段有@tblName,@RetColumns,@Orderfld,@PageSize,@PageIndex,@IsCount,@OrderType,@strWhere)</param>
    /// <returns>返回需求的页的数据(    DataTable)</returns>
    public static DataTable ExecuteProcedureGetTable(params SqlParameter[] parms)
    {
        DataTable dt = null;
        SqlConnection sqlcon = new SqlConnection(conn);
        try
        {
            SqlDataAdapter da = new SqlDataAdapter("GetRecordFromPage", sqlcon);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter pa in parms)
                da.SelectCommand.Parameters.Add(pa);
            dt = new DataTable();
            da.Fill(dt);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            sqlcon.Close();
        }
        return dt;
    }

    public static int ExecuteProcedure(string procName,params SqlParameter[] parms)
    {
        SqlConnection sqlcon = new SqlConnection(conn);
        try
        {
            SqlCommand cmd = new SqlCommand(procName, sqlcon);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter pa in parms)
                cmd.Parameters.Add(pa);
            sqlcon.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            sqlcon.Close();
        }
        return (int)parms[0].Value;
    }
}
